package w_tool

import (
	"database/sql"
	"encoding/json"
	"fmt"
	_ "github.com/wendal/go-oci8"
	"log"
	"os"
	"wzpsoso.com/w/t1/models"
)

type Words [][]string
type Word []string

func SQL_query(datasource string, SQL string) *Words {
	os.Setenv("NLS_LANG", "SIMPLIFIED CHINESE_CHINA.AL32UTF8")

	db, err := sql.Open("oci8", datasource)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.Query(SQL)
	if err != nil {
		log.Fatal("execute sql error")
		log.Fatal(err)
	}

	cols, _ := rows.Columns()
	rawResult := make([][]byte, len(cols))
	result := make([]string, len(cols))
	dest := make([]interface{}, len(cols))

	for i := range rawResult {
		dest[i] = &rawResult[i]
	}

	var words Words

	for rows.Next() {
		var w Word
		//fmt.Println("*")
		err = rows.Scan(dest...)
		if err != nil {
			log.Fatal(err)
		}
		for i, raw := range rawResult {
			if raw == nil {
				result[i] = ""
				w = append(w, "")
			} else {
				result[i] = string(raw)
				w = append(w, string(raw))
			}
			//fmt.Printf("%s ", result[i])
		}
		words = append(words, w)

	}
	rows.Close()

	return &words
}

func SQL_query_JSON(datasource string, SQL string) string {
	os.Setenv("NLS_LANG", "SIMPLIFIED CHINESE_CHINA.AL32UTF8")

	db, err := sql.Open("oci8", datasource)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.Query(SQL)
	if err != nil {
		log.Fatal("execute sql error")
		log.Fatal(err)
	}

	cols, _ := rows.Columns()
	rawResult := make([][]byte, len(cols))
	result := make([]string, len(cols))
	dest := make([]interface{}, len(cols))

	for i := range rawResult {
		dest[i] = &rawResult[i]
	}

	var words Words

	for rows.Next() {
		var w Word
		//fmt.Println("*")
		err = rows.Scan(dest...)
		if err != nil {
			log.Fatal(err)
		}
		for i, raw := range rawResult {
			if raw == nil {
				result[i] = ""
				w = append(w, "")
			} else {
				result[i] = string(raw)
				w = append(w, string(raw))
			}
			//fmt.Printf("%s ", result[i])
		}
		words = append(words, w)

	}
	rows.Close()

	jsonData, err := json.Marshal(words)
	if err != nil {
		fmt.Println("Marshal出错")
		log.Fatal(err)
	}
	return string(jsonData)
}

//不可用，对应反射
func SQL_query_O(datasource string, SQL string) interface{} {
	os.Setenv("NLS_LANG", "SIMPLIFIED CHINESE_CHINA.AL32UTF8")

	db, err := sql.Open("oci8", datasource)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	rows, err := db.Query(SQL)
	if err != nil {
		log.Fatal("execute sql error")
		log.Fatal(err)
	}

	var a models.Platform_alarm
	var b []models.Platform_alarm

	for rows.Next() {

		err = rows.Scan(&a.ID, &a.TABLE_NAME, &a.TABLE_CYCLE, &a.TABLE_LAYER, &a.ALARM_TIPS, &a.TABLE_TIME)
		if err != nil {
			log.Fatal(err)
		}
		//fmt.Println(a)
		b = append(b, a)
	}
	rows.Close()

	return &b
}

func SQL_execute(datasource string, SQL string) {
	os.Setenv("NLS_LANG", "SIMPLIFIED CHINESE_CHINA.AL32UTF8")

	db, err := sql.Open("oci8", datasource)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	result, err := db.Exec(SQL)
	if err != nil {
		log.Fatal(err)
	}
	affed, _ := result.RowsAffected()
	fmt.Println("影响行数 %s", affed)
}
